/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.session;

import com.thienloc.omap.entity.TblSubCp;
import com.thienloc.omap.jdbc.entity.SubCpProduction;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.slf4j.LoggerFactory;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author GiangLT
 */
@Stateless
public class TblSubCpFacade extends AbstractFacade<TblSubCp> {

    @PersistenceContext(unitName = "omapPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public TblSubCpFacade() {
        super(TblSubCp.class);
    }

    public List<SubCpProduction> countByTime(Integer month, Integer year, List<String> subCps, List<String> operators) {
        try {
            Calendar calendar = Calendar.getInstance();
            calendar.set(Calendar.MONTH, month - 1);
            calendar.set(Calendar.YEAR, year);

            int maxDayOfMonth = calendar.getActualMaximum(Calendar.DATE);
            int minDayOfMonth = calendar.getActualMinimum(Calendar.DATE);

            StringBuilder criteriaSuffix = new StringBuilder();
            criteriaSuffix.append(" and module_name in  (");
            for (String subCp : subCps) {
                criteriaSuffix.append("'").append(subCp).append("', ");
            }
            criteriaSuffix.delete(criteriaSuffix.lastIndexOf(", "), criteriaSuffix.length());
            criteriaSuffix.append(") ");

            criteriaSuffix.append(" and gen1 in  (");//operator
            for (String operator : operators) {
                criteriaSuffix.append("'").append(operator).append("', ");
            }
            criteriaSuffix.delete(criteriaSuffix.lastIndexOf(", "), criteriaSuffix.length());
            criteriaSuffix.append(") ");
            criteriaSuffix.append(" group by module_name, gen1");

            List<SubCpProduction> result = new ArrayList<SubCpProduction>();

            for (int i = minDayOfMonth; i <= maxDayOfMonth; i++) {
                calendar.set(Calendar.DATE, i);
                StringBuilder dateString = new StringBuilder(new SimpleDateFormat("dd/MM/yyyy").format(calendar.getTime()));

                StringBuilder queryStr = new StringBuilder();
                //<editor-fold defaultstate="collapsed" desc="query">
                queryStr.append(" SELECT module_name \"SUB_CP\", gen1, ");
                queryStr.append(" SUM(MO6071) \"MO6071\", SUM(MT6071) \"MO6071\", ");
                queryStr.append(" SUM(MO6171) \"MO6171\", SUM(MT6171) \"MO6171\", ");
                queryStr.append(" SUM(MO6271) \"MO6271\", SUM(MT6271) \"MO6271\", ");
                queryStr.append(" SUM(MO6371) \"MO6371\", SUM(MT6371) \"MO6371\", ");
                queryStr.append(" SUM(MO6471) \"MO6471\", SUM(MT6471) \"MO6471\", ");
                queryStr.append(" SUM(MO6571) \"MO6571\", SUM(MT6571) \"MO6571\", ");
                queryStr.append(" SUM(MO6671) \"MO6671\", SUM(MT6671) \"MO6671\", ");
                queryStr.append(" SUM(MO6771) \"MO6771\", SUM(MT6771) \"MO6771\" ");
                queryStr.append(" from ( ");

                //6071
                queryStr.append(" select module_name, gen1, ");
                queryStr.append(" count(*) \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6071' ");
                queryStr.append(" and type = 1 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", count(*) \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6071' ");
                queryStr.append(" and type = 0 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                //6171
                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" count(*) \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6171' ");
                queryStr.append(" and type = 1 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", count(*) \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6171' ");
                queryStr.append(" and type = 0 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                //6271
                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" count(*) \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6271' ");
                queryStr.append(" and type = 1 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", count(*) \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6271' ");
                queryStr.append(" and type = 0 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                //6371
                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" count(*) \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6371' ");
                queryStr.append(" and type = 1 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", count(*) \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6371' ");
                queryStr.append(" and type = 0 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                //6471
                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" count(*) \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6471' ");
                queryStr.append(" and type = 1 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", count(*) \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6471' ");
                queryStr.append(" and type = 0 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                //6571
                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" count(*) \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6571' ");
                queryStr.append(" and type = 1 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", count(*) \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6571' ");
                queryStr.append(" and type = 0 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                //6671
                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" count(*) \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6671' ");
                queryStr.append(" and type = 1 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", count(*) \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6671' ");
                queryStr.append(" and type = 0 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                //6771
                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" count(*) \"MO6771\", 0 \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6771' ");
                queryStr.append(" and type = 1 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" union all ");

                queryStr.append(" select module_name, gen1,  ");
                queryStr.append(" 0 \"MO6071\", 0 \"MT6071\",  ");
                queryStr.append(" 0 \"MO6171\", 0 \"MT6171\",  ");
                queryStr.append(" 0 \"MO6271\", 0 \"MT6271\", ");
                queryStr.append(" 0 \"MO6371\", 0 \"MT6371\", ");
                queryStr.append(" 0 \"MO6471\", 0 \"MT6471\", ");
                queryStr.append(" 0 \"MO6571\", 0 \"MT6571\", ");
                queryStr.append(" 0 \"MO6671\", 0 \"MT6671\", ");
                queryStr.append(" 0 \"MO6771\", count(*) \"MT6771\" ");
                queryStr.append(" from tbl_sub_cp  ");
                queryStr.append(" where sc = '6771' ");
                queryStr.append(" and type = 0 ");
                queryStr.append(" and to_char(time, 'dd/mm/yyyy') = '").append(dateString).append("' ");
                queryStr.append(criteriaSuffix);

                queryStr.append(" ) group by module_name, gen1  ");
                //</editor-fold>

                Query query = em.createNativeQuery(queryStr.toString());
                List resultList = query.getResultList();

                for (Object row : resultList) {
                    Object[] fields = (Object[]) row;
                    SubCpProduction production = new SubCpProduction();
                    production.setDate(calendar.getTime());
                    production.setSubCpName(fields[0].toString());
                    production.setOperator(fields[1].toString());
                    production.setMo6071(((BigDecimal) fields[2]).intValue());
                    production.setMt6071(((BigDecimal) fields[3]).intValue());
                    production.setMo6171(((BigDecimal) fields[4]).intValue());
                    production.setMt6171(((BigDecimal) fields[5]).intValue());
                    production.setMo6271(((BigDecimal) fields[6]).intValue());
                    production.setMt6271(((BigDecimal) fields[7]).intValue());
                    production.setMo6371(((BigDecimal) fields[8]).intValue());
                    production.setMt6371(((BigDecimal) fields[9]).intValue());
                    production.setMo6471(((BigDecimal) fields[10]).intValue());
                    production.setMt6471(((BigDecimal) fields[11]).intValue());
                    production.setMo6571(((BigDecimal) fields[12]).intValue());
                    production.setMt6571(((BigDecimal) fields[13]).intValue());
                    production.setMo6671(((BigDecimal) fields[14]).intValue());
                    production.setMt6671(((BigDecimal) fields[15]).intValue());
                    production.setMo6771(((BigDecimal) fields[16]).intValue());
                    production.setMt6771(((BigDecimal) fields[17]).intValue());

                    result.add(production);
                }
            }
            return result;
        } catch (NoResultException ex) {
            return null;
        } catch (Exception ex) {
            LoggerFactory.getLogger(RequestQueueFacade.class.getName()).error("", ex);
            return null;
        }
    }
}
